﻿/*
Commerce by Onex – eCommerce software platform (www.ecommerce.byonex.com)
Copyright (C) 2008-2010 by Onex Group

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Data;

using ShopModel;

namespace ShopDAO
{
    public class OrderDAO
    {
        private string connectionString;

        private SqlConnection Connection()
        {
            return new SqlConnection(connectionString);
        }

        public OrderDAO()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["csShop"].ConnectionString;
        }

        public int NewOrder(string name, string surname, string street, string city, string zipCode, int country,
            string email, string phone, int preferedLanguageID, string companyName, string companyNIP, string companyStreet, string companyCity, string companyZipCode,
            int paymentId, int shipmentId, decimal shipmentCost, string sessionId, decimal totalCost, string info, string urlReferer, object userid, bool createNewAccount, bool otherAddress, int? voucherId, bool NewsSubscription)
        {
            SqlConnection connection = Connection();

            SqlCommand command = new SqlCommand("NewOrder", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@name", name));
            command.Parameters.Add(new SqlParameter("@surname", surname));
            command.Parameters.Add(new SqlParameter("@street", street));
            command.Parameters.Add(new SqlParameter("@city", city));
            command.Parameters.Add(new SqlParameter("@zip_code", zipCode));
            if (country > 0)
            {
                command.Parameters.Add(new SqlParameter("@country", country));
            }
            command.Parameters.Add(new SqlParameter("@email", email));
            command.Parameters.Add(new SqlParameter("@phone", phone));
            command.Parameters.Add(new SqlParameter("@language_id", preferedLanguageID));
            command.Parameters.Add(new SqlParameter("@company_name", companyName));
            command.Parameters.Add(new SqlParameter("@company_nip", companyNIP));
            command.Parameters.Add(new SqlParameter("@company_street", companyStreet));
            command.Parameters.Add(new SqlParameter("@company_city", companyCity));
            command.Parameters.Add(new SqlParameter("@company_zip_code", companyZipCode));
            command.Parameters.Add(new SqlParameter("@payment_id", paymentId));
            command.Parameters.Add(new SqlParameter("@shipment_id", shipmentId));
            command.Parameters.Add(new SqlParameter("@session_id", sessionId));
            command.Parameters.Add(new SqlParameter("@shipment_cost", shipmentCost));
            command.Parameters.Add(new SqlParameter("@info", info));
            command.Parameters.Add(new SqlParameter("@url_referer", urlReferer));
            if (userid != null)
            {
                command.Parameters.Add(new SqlParameter("@user_id", (Guid)userid));
            }
            command.Parameters.Add(new SqlParameter("@create_account", createNewAccount));
            command.Parameters.Add(new SqlParameter("@other_address", otherAddress));
            command.Parameters.Add(new SqlParameter("@voucherid", voucherId));
            command.Parameters.Add(new SqlParameter("@newsSubscription", NewsSubscription));


            try
            {
                connection.Open();
                return Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                return -1;
            }
            finally
            {
                connection.Close();
            }
        }

        public List<Realization> GetDelayedPayments()
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("GetDelayedPayments", sqlC);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            List<Realization> result = new List<Realization>();
            Realization tmpRealization;

            try
            {
                sqlC.Open();

                SqlDataReader sqlR = command.ExecuteReader();

                while (sqlR.Read())
                {
                    string tmpEmail = (sqlR["Email"] == DBNull.Value) ? string.Empty : (string)sqlR["Email"];
                    string tmpCompanyName = (sqlR["CompanyName"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyName"];
                    string tmpCompanyNIP = (sqlR["CompanyNIP"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyNIP"];
                    string tmpCompanyStreet = (sqlR["CompanyStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyStreet"];
                    string tmpCompanyCity = (sqlR["CompanyCity"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyCity"];
                    string tmpCompanyZipCode = (sqlR["CompanyZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyZipCode"];

                    Customer tmpCustomer = new Customer((int)sqlR["IDcustomer"], (string)sqlR["FirstName"], (string)sqlR["Surname"], tmpEmail, (string)sqlR["Street"], (string)sqlR["ZipCode"], (string)sqlR["City"], tmpCompanyName, tmpCompanyNIP, tmpCompanyStreet, tmpCompanyCity, tmpCompanyZipCode, (string)sqlR["Country"], (string)sqlR["Phone"]);
                    tmpCustomer.PreferedLanguageID = (int)sqlR["PreferedLanguageID"];

                    DateTime tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                    string tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                    string tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                    string tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                    tmpRealization = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));
                    tmpRealization.iCustomer = tmpCustomer;
                    tmpRealization.PaymentNotificationSent = (bool)sqlR["PaymentNotificationSent"];
                    result.Add(tmpRealization);

                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public bool SetPaymentNotificationSent(int RealizationID, bool Sent)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();

            bool result = false;

            command.CommandText = "UPDATE Realizations SET PaymentNotificationSent=@Sent WHERE IDrealization=@RealizationID";
            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
            command.Parameters.Add(new SqlParameter("@Sent", Sent));

            try
            {
                sqlC.Open();
                result = command.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public Realization GetRealizationDetails(int RealizationID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            Realization result = null;

            try
            {
                sqlC.Open();

                com.CommandText = "SELECT * FROM  Realizations WHERE IDrealization=@RealizationID";
                com.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));

                SqlDataReader sqlR = com.ExecuteReader();
                if (sqlR.Read())
                {
                    DateTime tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                    string tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                    string tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                    string tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                    result = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));
                    result.ShipmentID = (sqlR["ShipmentID"] == DBNull.Value) ? -1 : (int)sqlR["ShipmentID"];
                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally { sqlC.Close(); }

            return result;
        }

        public Realization GetRealizationAllDetails(int RealizationID, int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            Realization result = null;

            try
            {
                sqlC.Open();

                com.CommandText = "SELECT Realizations.*,Customer.*,ra.IDrealizationAddress as RAId,ra.Street as RAStreet,ra.City as RACity,ra.ZipCode as RAZipCode, dbo.GetTextResource(c1.NameResourceID, @language) as RACountry,dbo.GetTextResource(c2.NameResourceID, @language) as CountryName, ra.CountryID as RACountryID ,ra.Name as RAName,ra.Surname as RASurname,ra.Email as RAEmail,ra.Phone as RAPhone " +
                                " FROM Realizations INNER JOIN Customer ON CustomerID=IDcustomer  LEFT OUTER JOIN RealizationAddress ra ON Realizations.RealizationAddressID = ra.IDrealizationAddress LEFT OUTER JOIN Countries c1 ON c1.IDcountry = ra.CountryID JOIN Countries c2 ON c2.IDcountry = Customer.CountryID WHERE IDrealization=@RealizationID";
                com.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
                com.Parameters.Add(new SqlParameter("@language", LanguageID));

                SqlDataReader sqlR = com.ExecuteReader();
                if (sqlR.Read())
                {
                    string tmpEmail = (sqlR["Email"] == DBNull.Value) ? string.Empty : (string)sqlR["Email"];
                    string tmpCompanyName = (sqlR["CompanyName"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyName"];
                    string tmpCompanyNIP = (sqlR["CompanyNIP"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyNIP"];
                    string tmpCompanyStreet = (sqlR["CompanyStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyStreet"];
                    string tmpCompanyCity = (sqlR["CompanyCity"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyCity"];
                    string tmpCompanyZipCode = (sqlR["CompanyZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyZipCode"];

                    Customer tmpCustomer = new Customer((int)sqlR["IDcustomer"], (string)sqlR["FirstName"], (string)sqlR["Surname"], tmpEmail, (string)sqlR["Street"], (string)sqlR["ZipCode"], (string)sqlR["City"], tmpCompanyName, tmpCompanyNIP, tmpCompanyStreet, tmpCompanyCity, tmpCompanyZipCode, (string)sqlR["CountryName"], (string)sqlR["Phone"]);
                    tmpCustomer.CountryID = (int)sqlR["CountryID"];
                    tmpCustomer.MembershipID = (Guid)sqlR["MembershipID"];

                    DateTime tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                    string tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                    string tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                    string tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                    result = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));
                    result.iCustomer = tmpCustomer;
                    result.ShipmentRealCost = (sqlR["ShipmentRealCost"] == DBNull.Value) ? 0 : (decimal)sqlR["ShipmentRealCost"];
                    result.ShipmentID = (sqlR["ShipmentID"] == DBNull.Value) ? -1 : (int)sqlR["ShipmentID"];

                    result.RealizationAdress = new RealizationAdress();
                    result.RealizationAdress.City = (sqlR["RACity"] == DBNull.Value) ? string.Empty : (string)sqlR["RACity"];
                    result.RealizationAdress.Country = (sqlR["RACountry"] == DBNull.Value) ? string.Empty : (string)sqlR["RACountry"];
                    result.RealizationAdress.CountryID = sqlR["RACountryID"] == DBNull.Value ? -1 : (int)sqlR["RACountryID"];
                    result.RealizationAdress.Email = (sqlR["RAEmail"] == DBNull.Value) ? string.Empty : (string)sqlR["RAEmail"];
                    result.RealizationAdress.FirstName = (sqlR["RAName"] == DBNull.Value) ? string.Empty : (string)sqlR["RAName"];
                    result.RealizationAdress.ID = (sqlR["RAId"] == DBNull.Value) ? -1 : (int)sqlR["RAId"];
                    result.RealizationAdress.LastName = (sqlR["RASurname"] == DBNull.Value) ? string.Empty : (string)sqlR["RASurname"];
                    result.RealizationAdress.Phone = (sqlR["RAPhone"] == DBNull.Value) ? string.Empty : (string)sqlR["RAPhone"];
                    result.RealizationAdress.Street = (sqlR["RAStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["RAStreet"];
                    result.RealizationAdress.ZipCode = (sqlR["RAZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["RAZipCode"];

                    result.PaymentNotificationSent = (bool)sqlR["PaymentNotificationSent"];
                }
                sqlR.Close();

                result.OrderedProducts = GetRealizationOrderedProductsList(RealizationID, LanguageID);
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public List<ProductBatch> GetRealizationOrderedProductsList(int RealizationID, int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            SqlDataReader sqlR;

            List<ProductBatch> result = new List<ProductBatch>();

            try
            {
                sqlC.Open();

                com.CommandText = "GetRealizationOrderedProducts";
                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
                com.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

                ProductBatch tmpProductBatch;

                sqlR = com.ExecuteReader();

                while (sqlR.Read())
                {
                    tmpProductBatch = new ProductBatch((int)sqlR["ProductsID"], (string)sqlR["Name"], -1, (decimal)sqlR["Price"], (decimal)sqlR["Cost"], (int)sqlR["AvailableCount"], (int)sqlR["OrderedCount"], (decimal)sqlR["VatRateValue"], (decimal)sqlR["Discount"]);
                    tmpProductBatch.Status = (OrderedProductStatus)(int)sqlR["StatusID"];

                    result.Add(tmpProductBatch);
                }
            }

            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public bool CheckIfSpecimensMissing(int RealizationID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();

            bool result = false;

            command.CommandText = "CheckIfSpecimensMissing";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));

            try
            {
                sqlC.Open();
                SqlDataReader sqlR = command.ExecuteReader();
                if (sqlR.Read())
                    result = true;
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;

        }

        public List<Realization> GetRealizationsWithMissingProducts(int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();

            List<Realization> result = new List<Realization>();
            Realization tmpRealization = null;
            Customer tmpCustomer;

            command.CommandText = "SELECT c.*, r.*, o.* FROM Realizations AS r INNER JOIN Customer AS c ON r.CustomerID = c.IDcustomer INNER JOIN(SELECT ProductsID, RealizationID, dbo.GetTextResource(NameResourceID, @LanguageID) AS Name , COUNT(*) AS OrderedCount, Available AS AvailableCount FROM OrderedProducts WHERE StatusID = 1  GROUP BY ProductsID, RealizationID, NameResourceID, Available) AS o ON r.IDrealization = o.RealizationID ORDER BY r.IDrealization";
            command.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

            try
            {
                sqlC.Open();

                SqlDataReader sqlR = command.ExecuteReader();
                while (sqlR.Read())
                {
                    if (!result.Exists(delegate(Realization r) { return r.IDRealization == (int)sqlR["IDrealization"]; }))
                    {
                        string tmpEmail = (sqlR["Email"] == DBNull.Value) ? string.Empty : (string)sqlR["Email"];
                        string tmpCompanyName = (sqlR["CompanyName"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyName"];
                        string tmpCompanyNIP = (sqlR["CompanyNIP"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyNIP"];
                        string tmpCompanyStreet = (sqlR["CompanyStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyStreet"];
                        string tmpCompanyCity = (sqlR["CompanyCity"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyCity"];
                        string tmpCompanyZipCode = (sqlR["CompanyZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyZipCode"];

                        tmpCustomer = new Customer((int)sqlR["IDcustomer"], (string)sqlR["FirstName"], (string)sqlR["Surname"], tmpEmail, (string)sqlR["Street"], (string)sqlR["ZipCode"], (string)sqlR["City"], tmpCompanyName, tmpCompanyNIP, tmpCompanyStreet, tmpCompanyCity, tmpCompanyZipCode, (string)sqlR["Country"], (string)sqlR["Phone"]);

                        DateTime tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                        string tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                        string tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                        string tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                        tmpRealization = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));
                        tmpRealization.iCustomer = tmpCustomer;

                        result.Add(tmpRealization);
                    }

                    tmpRealization.OrderedProducts.Add(new ProductBatch((int)sqlR["ProductsID"], (string)sqlR["Name"], -1, -1, -1, (int)sqlR["AvailableCount"], (int)sqlR["OrderedCount"], 0));
                }
                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public List<ProductBatch> GetRealizationMissingProductsList(int RealizationID, int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            SqlDataReader sqlR;

            List<ProductBatch> result = new List<ProductBatch>();

            try
            {
                sqlC.Open();

                com.CommandText = "SELECT o.ProductsID, dbo.GetTextResource(o.NameResourceID,@LanguageID) AS Name , COUNT(*) AS OrderedCount, o.Available AS AvailableCount" +
                  " FROM OrderedProducts o " +
                  " WHERE (o.RealizationID = @RealizationID) AND o.StatusID = 1 " +
                  " GROUP BY o.ProductsID, o.NameResourceID, o.Available";

                com.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
                com.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

                sqlR = com.ExecuteReader();

                while (sqlR.Read())
                {
                    result.Add(new ProductBatch((int)sqlR["ProductsID"], (string)sqlR["Name"], -1, -1, -1, (int)sqlR["AvailableCount"], (int)sqlR["OrderedCount"], 0));
                }
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public List<ProductBatch> GetMissingProductsList(int? LanguageID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            SqlDataReader sqlR;

            List<ProductBatch> result = new List<ProductBatch>();

            try
            {
                sqlC.Open();

                com.CommandText = "SELECT o.ProductsID, dbo.GetTextResource(o.NameResourceID,@LanguageID) AS Name ,COUNT(*) AS OrderedCount, o.Available AS AvailableCount FROM OrderedProducts o WHERE o.StatusID = 1 GROUP BY o.ProductsID, o.NameResourceID, o.Available";
                com.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

                sqlR = com.ExecuteReader();

                while (sqlR.Read())
                    result.Add(new ProductBatch((int)sqlR["ProductsID"], (string)sqlR["Name"], -1, -1, -1, (int)sqlR["AvailableCount"], (int)sqlR["OrderedCount"], 0));

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public List<Realization> GetRealizationList(string Customer, string ID, string PaymentType, string StatusID, string OnlyActive, ref int pageNumber, int pageSize, out int orderCount, string productID)
        {
            string query = string.Empty;
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            List<Realization> result = new List<Realization>();
            Realization tmpRealization;

            com.CommandText = "SearchOrder";
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add(new SqlParameter("@page_size", pageSize));

            SqlParameter inout = new SqlParameter("@page_number", SqlDbType.Int);
            inout.Direction = ParameterDirection.InputOutput;
            inout.Value = pageNumber;
            com.Parameters.Add(inout);

            SqlParameter output = new SqlParameter("@order_count", SqlDbType.Int);
            output.Direction = ParameterDirection.Output;
            com.Parameters.Add(output);

            query = "SELECT Realizations.*, Customer.* FROM Realizations INNER JOIN Customer ON CustomerID=IDcustomer " + RealizationBuildFilterQuery(Customer, ID, PaymentType, StatusID, OnlyActive, productID);

            com.Parameters.Add(new SqlParameter("@query", query));

            try
            {
                sqlC.Open();

                SqlDataReader sqlR = com.ExecuteReader();
                while (sqlR.Read())
                {
                    string tmpEmail = (sqlR["Email"] == DBNull.Value) ? string.Empty : (string)sqlR["Email"];
                    string tmpCompanyName = (sqlR["CompanyName"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyName"];
                    string tmpCompanyNIP = (sqlR["CompanyNIP"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyNIP"];
                    string tmpCompanyStreet = (sqlR["CompanyStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyStreet"];
                    string tmpCompanyCity = (sqlR["CompanyCity"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyCity"];
                    string tmpCompanyZipCode = (sqlR["CompanyZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyZipCode"];

                    Customer tmpCustomer = new Customer((int)sqlR["IDcustomer"], (string)sqlR["FirstName"], (string)sqlR["Surname"], tmpEmail, (string)sqlR["Street"], (string)sqlR["ZipCode"], (string)sqlR["City"], tmpCompanyName, tmpCompanyNIP, tmpCompanyStreet, tmpCompanyCity, tmpCompanyZipCode, (string)sqlR["Country"], (string)sqlR["Phone"]);
                    tmpCustomer.PreferedLanguageID = (int)sqlR["PreferedLanguageID"];
                    DateTime tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                    string tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                    string tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                    string tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                    tmpRealization = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));
                    tmpRealization.ShipmentID = (sqlR["ShipmentID"] == DBNull.Value) ? -1 : (int)sqlR["ShipmentID"];

                    tmpRealization.iCustomer = tmpCustomer;

                    tmpRealization.MissingSpecimen = CheckIfSpecimensMissing(tmpRealization.IDRealization);
                    tmpRealization.PaymentNotificationSent = (bool)sqlR["PaymentNotificationSent"];

                    result.Add(tmpRealization);
                }

                sqlR.Close();
                orderCount = (int)com.Parameters["@order_count"].Value;
                pageNumber = (int)com.Parameters["@page_number"].Value;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                orderCount = 0;
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public List<Realization> GetRealizationListForCustomer(Guid userId, bool onlyActiv)
        {
            List<Realization> result = new List<Realization>();
            Realization realization;
            string tmpEmail;
            string tmpCompanyName;
            string tmpCompanyNIP;
            string tmpCompanyStreet;
            string tmpCompanyCity;
            string tmpCompanyZipCode;
            Customer tmpCustomer;
            DateTime tmpEndDate;
            string tmpTransferID;
            string tmpInfo;
            string tmpUrlReferer;


            SqlConnection connection = Connection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Realizations r INNER JOIN Customer c ON r.CustomerID = c.IDcustomer WHERE c.MembershipID = @user_id";
            if (onlyActiv)
            {
                command.CommandText += " AND r.StatusID not in (4,6,7) ";
            }
            else
            {
                command.CommandText += " AND r.StatusID in (4,6,7) ";
            }

            command.CommandText += " ORDER BY IdRealization desc;";
            command.Parameters.Add(new SqlParameter("@user_id", userId));

            connection.Open();
            SqlDataReader sqlR = command.ExecuteReader();
            while (sqlR.Read())
            {
                tmpEmail = (sqlR["Email"] == DBNull.Value) ? string.Empty : (string)sqlR["Email"];
                tmpCompanyName = (sqlR["CompanyName"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyName"];
                tmpCompanyNIP = (sqlR["CompanyNIP"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyNIP"];
                tmpCompanyStreet = (sqlR["CompanyStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyStreet"];
                tmpCompanyCity = (sqlR["CompanyCity"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyCity"];
                tmpCompanyZipCode = (sqlR["CompanyZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyZipCode"];

                tmpCustomer = new Customer((int)sqlR["IDcustomer"], (string)sqlR["FirstName"], (string)sqlR["Surname"], tmpEmail, (string)sqlR["Street"], (string)sqlR["ZipCode"], (string)sqlR["City"], tmpCompanyName, tmpCompanyNIP, tmpCompanyStreet, tmpCompanyCity, tmpCompanyZipCode, (string)sqlR["Country"], (string)sqlR["Phone"]);

                tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                realization = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));
                realization.iCustomer = tmpCustomer;
                realization.ShipmentRealCost = (sqlR["ShipmentRealCost"] == DBNull.Value) ? 0 : (decimal)sqlR["ShipmentRealCost"];
                realization.ShipmentID = (sqlR["ShipmentID"] == DBNull.Value) ? -1 : (int)sqlR["ShipmentID"];

                realization.PaymentNotificationSent = (bool)sqlR["PaymentNotificationSent"];
                realization.OrderedProducts = null;
                result.Add(realization);
            }

            sqlR.Close();

            return result;
        }

        public List<Realization> GetRealizationList(int StatusID, ref int pageNumber, int pageSize, out int orderCount, bool? IncludeMissing)
        {
            string query = string.Empty;
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            List<Realization> result = new List<Realization>();
            Realization tmpRealization;
            com.CommandText = "SearchOrder";
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add(new SqlParameter("@page_size", pageSize));

            SqlParameter inout = new SqlParameter("@page_number", SqlDbType.Int);
            inout.Direction = ParameterDirection.InputOutput;
            inout.Value = pageNumber;
            com.Parameters.Add(inout);

            SqlParameter output = new SqlParameter("@order_count", SqlDbType.Int);
            output.Direction = ParameterDirection.Output;
            com.Parameters.Add(output);

            try
            {
                sqlC.Open();

                if (StatusID != -1)
                {
                    query = string.Format("SELECT Realizations.*, Customer.* FROM Realizations INNER JOIN Customer ON CustomerID=IDcustomer WHERE StatusID={0}", StatusID);
                    if (IncludeMissing != null && !IncludeMissing.Value)
                    {
                        query += " AND IdRealization NOT IN(SELECT DISTINCT RealizationID FROM Orders WHERE StatusID=1)";
                    }
                }
                else
                {
                    query = "SELECT Realizations.*, Customer.* FROM Realizations INNER JOIN Customer ON CustomerID=IDcustomer";
                }
                com.Parameters.Add(new SqlParameter("@query", query));
                SqlDataReader sqlR = com.ExecuteReader();
                while (sqlR.Read())
                {
                    string tmpEmail = (sqlR["Email"] == DBNull.Value) ? string.Empty : (string)sqlR["Email"];
                    string tmpCompanyName = (sqlR["CompanyName"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyName"];
                    string tmpCompanyNIP = (sqlR["CompanyNIP"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyNIP"];
                    string tmpCompanyStreet = (sqlR["CompanyStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyStreet"];
                    string tmpCompanyCity = (sqlR["CompanyCity"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyCity"];
                    string tmpCompanyZipCode = (sqlR["CompanyZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyZipCode"];

                    Customer tmpCustomer = new Customer((int)sqlR["IDcustomer"], (string)sqlR["FirstName"], (string)sqlR["Surname"], tmpEmail, (string)sqlR["Street"], (string)sqlR["ZipCode"], (string)sqlR["City"], tmpCompanyName, tmpCompanyNIP, tmpCompanyStreet, tmpCompanyCity, tmpCompanyZipCode, (string)sqlR["Country"], (string)sqlR["Phone"]);
                    tmpCustomer.PreferedLanguageID = (int)sqlR["PreferedLanguageID"];

                    DateTime tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                    string tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                    string tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                    string tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                    tmpRealization = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));
                    tmpRealization.iCustomer = tmpCustomer;
                    tmpRealization.ShipmentID = (sqlR["ShipmentID"] == DBNull.Value) ? -1 : (int)sqlR["ShipmentID"];

                    tmpRealization.MissingSpecimen = CheckIfSpecimensMissing(tmpRealization.IDRealization);
                    tmpRealization.PaymentNotificationSent = (bool)sqlR["PaymentNotificationSent"];

                    result.Add(tmpRealization);
                }

                sqlR.Close();

                orderCount = (int)output.Value;
                pageNumber = (int)inout.Value;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                orderCount = 0;
            }
            finally
            { sqlC.Close(); }

            return result;

        }

        public bool UpdateRealization(int IDRealization, DateTime EndDate, string TransferID, int StatusID, string Info, string UrlReferer, int PaymentID, decimal ShipmentCost, decimal ShipmentRealCost, bool PaymentNotificationSent, int ShipmentID, DateTime? InvoiceDate, DateTime? DelayedPaymentDate)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            int result = -1;

            com.CommandText = "UPDATE Realizations SET EndDate=@EndDate, TransferID=@TransferID, StatusID=@StatusID, Info=@Info, UrlReferer=@Urlreferer, ShipmentCost=@ShipmentCost, PaymentID=@PaymentID,ShipmentRealCost=@ShipmentRealCost, PaymentNotificationSent=@PaymentNotificationSent, ShipmentID=@ShipmentID, InvoiceDate=@InvoiceDate, DelayedPaymentDate=@DelayedPaymentDate WHERE IDrealization=@RealizationID";

            com.Parameters.Add(new SqlParameter("@TransferID", TransferID));
            com.Parameters.Add(new SqlParameter("@StatusID", StatusID));
            com.Parameters.Add(new SqlParameter("@Info", Info));
            com.Parameters.Add(new SqlParameter("@UrlReferer", UrlReferer));
            com.Parameters.Add(new SqlParameter("@PaymentID", PaymentID));
            com.Parameters.Add(new SqlParameter("@ShipmentCost", ShipmentCost));
            com.Parameters.Add(new SqlParameter("@RealizationID", IDRealization));
            com.Parameters.Add(new SqlParameter("@ShipmentRealCost", ShipmentRealCost));
            com.Parameters.Add(new SqlParameter("@PaymentNotificationSent", PaymentNotificationSent));
            com.Parameters.Add(new SqlParameter("@ShipmentID", ShipmentID));

            if (EndDate.Year != 1)
                com.Parameters.Add(new SqlParameter("@EndDate", EndDate));
            else
                com.Parameters.Add(new SqlParameter("@EndDate", DBNull.Value));

            if (InvoiceDate.HasValue)
                com.Parameters.Add(new SqlParameter("@InvoiceDate", InvoiceDate.Value));
            else
                com.Parameters.Add(new SqlParameter("@InvoiceDate", DBNull.Value));

            if (DelayedPaymentDate.HasValue)
                com.Parameters.Add(new SqlParameter("@DelayedPaymentDate", DelayedPaymentDate.Value));
            else
                com.Parameters.Add(new SqlParameter("@DelayedPaymentDate", DBNull.Value));

            try
            {
                sqlC.Open();

                result = com.ExecuteNonQuery();
                if (StatusID == (int)RealizationStatus.ToSend || StatusID == (int)RealizationStatus.Finished)
                    UpdateRealizationMissingSpecimens(IDRealization);
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result != -1;
        }

        public void RemoveProductFromRealization(int RealizationID, int ProductID, int StatusID, int Count)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = new SqlCommand("UpdateRealizationRemoveProduct", sqlC);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
            com.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            com.Parameters.Add(new SqlParameter("@StatusID", StatusID));
            com.Parameters.Add(new SqlParameter("@Count", Count));

            try
            {
                sqlC.Open();

                com.ExecuteScalar();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
                IncludeVoucherDiscount(RealizationID, null);
            }
        }

        public void AddProductToRealization(int RealizationID, int ProductID, int Count)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("UpdateRealizationAddProduct", sqlC);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
            command.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            command.Parameters.Add(new SqlParameter("@Count", Count));

            try
            {
                sqlC.Open();

                command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
                IncludeVoucherDiscount(RealizationID, null);
            }
        }

        public void UpdateRealizationSetSend(int RealizationID, int StatusID, DateTime EndDate, decimal ShipmentRealCost)
        {

            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            com.CommandText = "UPDATE Realizations SET StatusID=@statusId, EndDate=@enddate, ShipmentRealCost=@ShipmentRealCost WHERE IDRealization=@realizatId";
            com.Parameters.Add(new SqlParameter("@realizatId", RealizationID));
            com.Parameters.Add(new SqlParameter("@enddate", EndDate));
            com.Parameters.Add(new SqlParameter("@ShipmentRealCost", ShipmentRealCost));
            com.Parameters.Add(new SqlParameter("@statusId", StatusID));
            try
            {
                sqlC.Open();
                com.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }
        }

        public void UpdateRealizationSetFinished(int RealizationID)
        {

            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            com.CommandText = "UPDATE Realizations SET StatusID=4 WHERE IDRealization=@realizatId";
            com.Parameters.Add(new SqlParameter("@realizatId", RealizationID));
            try
            {
                sqlC.Open();
                com.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }
        }

        public void UpdateRealizationSetPaid(int RealizationID, int StatusID, string TransferID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            com.CommandText = "UPDATE Realizations SET StatusID=@statusId, TransferID=@transferId WHERE IDRealization=@realizatId";
            com.Parameters.Add(new SqlParameter("@realizatId", RealizationID));
            com.Parameters.Add(new SqlParameter("@transferId", TransferID));
            com.Parameters.Add(new SqlParameter("@statusId", StatusID));

            try
            {
                sqlC.Open();
                com.ExecuteNonQuery();
                UpdateRealizationMissingSpecimens(RealizationID);
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }
        }

        public void UpdateRealizationSetStatus(int realizationID, int statusID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = sqlC.CreateCommand();
            com.CommandText = "UPDATE Realizations SET StatusID=@statusId WHERE IDRealization=@realizatId";
            com.Parameters.Add(new SqlParameter("@realizatId", realizationID));
            com.Parameters.Add(new SqlParameter("@statusId", statusID));
            try
            {
                sqlC.Open();
                com.ExecuteNonQuery();
                if (statusID == (int)RealizationStatus.ToSend)
                    UpdateRealizationMissingSpecimens(realizationID);
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }
        }

        public void UpdateRealizationSetDelayedPayment(int realizationID)
        {
            SqlConnection connection = Connection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "UPDATE Realizations SET PaymentID = 14, StatusID = 3 WHERE IDrealization = @id";
            command.Parameters.Add(new SqlParameter("@id", realizationID));

            try
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                connection.Close();
            }
        }

        public void CancelRealization(int RealizationID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("CancelRealization", sqlC);

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));

            try
            {
                sqlC.Open();
                command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }
        }

        public void UpdateRealizationMissingSpecimens(int RealizationID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("UpdateRealizationMissingSpecimens", sqlC);

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));

            try
            {
                sqlC.Open();
                command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }
        }

        public void UptateOrderedProductPrice(int RealizationID, int ProductID, decimal NewPrice)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("UpdateRealizationOrderedProductsPrice", sqlC);

            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
            command.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            command.Parameters.Add(new SqlParameter("@NewPrice", NewPrice));

            command.CommandType = CommandType.StoredProcedure;



            try
            {
                sqlC.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
                IncludeVoucherDiscount(RealizationID, null);
            }
        }

        public void CancelOrderedProduct(int RealizationID, int ProductID, int StatusID, int Count)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("UpdateRealizationCancelProduct", sqlC);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
            command.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            command.Parameters.Add(new SqlParameter("@StatusID", StatusID));
            command.Parameters.Add(new SqlParameter("@Count", Count));

            try
            {
                sqlC.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
                IncludeVoucherDiscount(RealizationID, null);
            }
        }

        public void ReturnOrderedProduct(int RealizationID, int ProductID, int StatusID, int Count)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = new SqlCommand("UpdateRealizationReturnProduct", sqlC);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
            command.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            command.Parameters.Add(new SqlParameter("@StatusID", StatusID));
            command.Parameters.Add(new SqlParameter("@Count", Count));

            try
            {
                sqlC.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            {
                sqlC.Close();
                IncludeVoucherDiscount(RealizationID, null);
            }
        }

        public Realization GetLastRealization()
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;

            Realization result = null;

            command.CommandText = "SELECT TOP(1) r.*, c.* FROM Realizations r JOIN Customer c ON r.CustomerID=c.IDcustomer ORDER BY r.IDrealization DESC";

            try
            {
                sqlC.Open();

                sqlR = command.ExecuteReader();

                if (sqlR.Read())
                {
                    DateTime tmpEndDate = (sqlR["EndDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sqlR["EndDate"];
                    string tmpTransferID = (sqlR["TransferID"] == DBNull.Value) ? string.Empty : (string)sqlR["TransferID"];
                    string tmpInfo = (sqlR["Info"] == DBNull.Value) ? string.Empty : (string)sqlR["Info"];
                    string tmpUrlReferer = (sqlR["UrlReferer"] == DBNull.Value) ? string.Empty : (string)sqlR["UrlReferer"];

                    result = new Realization((int)sqlR["IDrealization"], (int)sqlR["CustomerID"], (DateTime)sqlR["StartDate"], tmpEndDate, tmpTransferID, (RealizationStatus)sqlR["StatusID"], (decimal)sqlR["TotalAmount"], (decimal)sqlR["TotalAmountBrutto"], tmpInfo, tmpUrlReferer, (int)sqlR["PaymentID"], (decimal)sqlR["ShipmentCost"], (sqlR["InvoiceNumber"] == DBNull.Value ? string.Empty : (string)sqlR["InvoiceNumber"]), (sqlR["InvoiceDate"] == DBNull.Value ? null : (DateTime?)sqlR["InvoiceDate"]), (sqlR["DelayedPaymentDate"] == DBNull.Value ? null : (DateTime?)sqlR["DelayedPaymentDate"]));

                    string tmpEmail = (sqlR["Email"] == DBNull.Value) ? string.Empty : (string)sqlR["Email"];
                    string tmpCompanyName = (sqlR["CompanyName"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyName"];
                    string tmpCompanyNIP = (sqlR["CompanyNIP"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyNIP"];
                    string tmpCompanyStreet = (sqlR["CompanyStreet"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyStreet"];
                    string tmpCompanyCity = (sqlR["CompanyCity"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyCity"];
                    string tmpCompanyZipCode = (sqlR["CompanyZipCode"] == DBNull.Value) ? string.Empty : (string)sqlR["CompanyZipCode"];

                    result.iCustomer = new Customer((int)sqlR["IDcustomer"], (string)sqlR["FirstName"], (string)sqlR["Surname"], tmpEmail, (string)sqlR["Street"], (string)sqlR["ZipCode"], (string)sqlR["City"], tmpCompanyName, tmpCompanyNIP, tmpCompanyStreet, tmpCompanyCity, tmpCompanyZipCode, (string)sqlR["Country"], (string)sqlR["Phone"]);
                }

                sqlR.Close();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        public List<RealizationLog> GetRealizationLog(int RealizationID)
        {
            SqlConnection sqlC = Connection();
            SqlCommand command = sqlC.CreateCommand();
            SqlDataReader sqlR;

            List<RealizationLog> result = new List<RealizationLog>();

            command.CommandText = "SELECT * FROM RealizationsLog WHERE RealizationsID=@RealizationID";
            command.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));

            try
            {
                sqlC.Open();
                sqlR = command.ExecuteReader();

                RealizationLog tmpRealizationLog;

                while (sqlR.Read())
                {
                    tmpRealizationLog = new RealizationLog((DateTime)sqlR["Date"], (string)sqlR["Content"]);
                    result.Add(tmpRealizationLog);
                }
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }

            return result;
        }

        private string RealizationBuildFilterQuery(string Customer, string ID, string PaymentType, string StatusID, string OnlyActive, string ProductId)
        {
            StringBuilder query = new StringBuilder();

            if ((Customer != "") || (ID != "") || (PaymentType != "") || (StatusID != "") || (OnlyActive != "") || (ProductId != ""))
            {
                query.Append(" WHERE");

                if (Customer != "" && !Regex.IsMatch(Customer, @"[^\d\w\s.,]"))
                    query.Append(String.Format(" (Customer.CompanyName+' '+Customer.FirstName+' '+Customer.Surname LIKE '%{0}%')", Customer));

                if (ID != "")
                {
                    int tmpInt = -1;
                    if (int.TryParse(ID, out tmpInt))
                    {
                        if (!query.ToString().EndsWith("WHERE"))
                            query.Append(" AND");
                        query.Append(String.Format(" Realizations.IDrealization={0}", tmpInt));
                    }
                }


                if (PaymentType != "" && !Regex.IsMatch(Customer, @"[^\d\w\s.,]"))
                {
                    if (!query.ToString().EndsWith("WHERE"))
                        query.Append(" AND");
                    query.Append(String.Format(" PaymentID IN (SELECT IDpayment FROM PaymentTypes WHERE PaymentType='{0}')", PaymentType));
                }

                if (StatusID != "")
                {
                    int tmpInt = -1;
                    if (int.TryParse(StatusID, out tmpInt))
                    {
                        if (!query.ToString().EndsWith("WHERE"))
                            query.Append(" AND");
                        query.Append(String.Format(" Realizations.StatusID={0}", tmpInt));
                    }
                }

                if (OnlyActive != "")
                {
                    bool tmpBool = false;
                    if (bool.TryParse(OnlyActive, out tmpBool))
                    {
                        if (!query.ToString().EndsWith("WHERE"))
                            query.Append(" AND");
                        query.Append(String.Format(" Realizations.StatusID IN (1,2,3,5)"));
                    }
                }


                if (ProductId != "")
                {
                    int pid = -1;
                    if (int.TryParse(ProductId, out pid))
                    {
                        if (!query.ToString().EndsWith("WHERE"))
                            query.Append(" AND");
                        query.Append(String.Format(" Realizations.IDrealization IN (select distinct RealizationId from Orders where ProductsId={0})", pid));
                    }
                }

                if (query.ToString().EndsWith("WHERE"))
                    return string.Empty;

            }
            return query.ToString();
        }

        public bool UpdateRealizationAddress(int IDRealizationAddress, string FirstName, string Surname, string Street, string ZipCode, string City, int CountryID)
        {
            SqlConnection conncetion = Connection();
            SqlCommand command = conncetion.CreateCommand();

            command.CommandText = "UPDATE RealizationAddress SET Name = @name, Surname = @surname, Street = @street, ZipCode = @zipcode, City = @city, CountryID = @country WHERE IDRealizationAddress = @id";
            command.Parameters.Add(new SqlParameter("@name", FirstName));
            command.Parameters.Add(new SqlParameter("@surname", Surname));
            command.Parameters.Add(new SqlParameter("@street", Street));
            command.Parameters.Add(new SqlParameter("@zipcode", ZipCode));
            command.Parameters.Add(new SqlParameter("@city", City));
            command.Parameters.Add(new SqlParameter("@country", CountryID));
            command.Parameters.Add(new SqlParameter("@id", IDRealizationAddress));

            try
            {
                conncetion.Open();
                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                return false;
            }
            finally
            {
                conncetion.Close();
            }
        }

        public int[] GetAllStatusIDs()
        {
            List<int> result = null;
            SqlConnection connection = Connection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT IDstatus FROM RealizationStatus";

            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                result = new List<int>();
                while (reader.Read())
                {
                    result.Add(reader.GetInt32(0));
                }

                reader.Close();

                return result.ToArray();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                connection.Close();
                return null;
            }
        }

        public void IncludeVoucherDiscount(int RealizationID, int? VoucherId)
        {
            SqlConnection sqlC = Connection();
            SqlCommand com = new SqlCommand("IncludeVoucherDiscount", sqlC);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add(new SqlParameter("@RealizationID", RealizationID));
            com.Parameters.Add(new SqlParameter("@VoucherId", VoucherId));

            try
            {
                sqlC.Open();

                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
            }
            finally
            { sqlC.Close(); }
        }
    }
}

